DROP TRIGGER UPDATE_EMPLOYEE_2_ID_TRI;
DROP TRIGGER UPDATE_POSITION_2_ID_TRI;
DROP TRIGGER UPDATE_DEPARTMENT_2_ID_TRI;
DROP SEQUENCE EMPLOYEE_ID_2_SEQ;
DROP SEQUENCE POSITION_ID_2_SEQ;
DROP SEQUENCE DEPARTMENT_ID_2_SEQ;
DROP TABLE EMPLOYEES_2;
DROP TABLE POSITIONS_2;
DROP TABLE DEPARTMENTS_2;

--创建表
CREATE TABLE DEPARTMENTS_2(
  DEPARTMENT_ID INT PRIMARY KEY,
  DEPARTMENT_NAME VARCHAR2(20) NOT NULL
);

CREATE TABLE POSITIONS_2(
  POSITION_ID INT PRIMARY KEY,
  POSITION_NAME VARCHAR2(20) NOT NULL
);

CREATE TABLE EMPLOYEES_2(
  EMPLOYEE_ID INT PRIMARY KEY,
  EMPLOYEE_NAME VARCHAR2(20) NOT NULL,
  DEPARTMENT_ID INT REFERENCES DEPARTMENTS_2(DEPARTMENT_ID),
  POSITION_ID INT REFERENCES POSITIONS_2(POSITION_ID)
);

--创建序列
CREATE SEQUENCE DEPARTMENT_ID_2_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE;

CREATE SEQUENCE POSITION_ID_2_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE;

CREATE SEQUENCE EMPLOYEE_ID_2_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE;

--创建触发器
CREATE TRIGGER UPDATE_DEPARTMENT_2_ID_TRI BEFORE
INSERT ON DEPARTMENTS_2 FOR EACH ROW WHEN(NEW.DEPARTMENT_ID IS NULL)
BEGIN
SELECT DEPARTMENT_ID_2_SEQ.NEXTVAL INTO:NEW.DEPARTMENT_ID FROM DUAL;
END;

CREATE TRIGGER UPDATE_POSITION_2_ID_TRI BEFORE
INSERT ON POSITIONS_2 FOR EACH ROW WHEN(NEW.POSITION_ID IS NULL)
BEGIN
SELECT POSITION_ID_2_SEQ.NEXTVAL INTO:NEW.POSITION_ID FROM DUAL;
END;

CREATE TRIGGER UPDATE_EMPLOYEE_2_ID_TRI BEFORE
INSERT ON EMPLOYEES_2 FOR EACH ROW WHEN(NEW.EMPLOYEE_ID IS NULL)
BEGIN
SELECT EMPLOYEE_ID_2_SEQ.NEXTVAL INTO:NEW.EMPLOYEE_ID FROM DUAL;
END;
